from datetime import datetime
import json
import time

import pandas as pd
import requests
from openpyxl import Workbook


def CETQuery(km, xm, no):
    url = 'https://cachecloud.neea.cn/api/latest/results/cet'
    headers = {
        'Accept-Language': 'zh-CN,zh-Hans;q=0.9',
        'Origin': 'https://cjcx.neea.edu.cn',
        'Referer': 'https://cjcx.neea.edu.cn/',
        'Sec-Fetch-Dest': 'empty',
        'Sec-Fetch-Mode': 'cors',
        'Sec-Fetch-Site': 'cross-site',
    }
    item = ['CET4', 'CET6']
    params = {
        'km': km,  # 考试科目1为四级, 2位六级
        'xm': xm,  # 姓名
        'no': no,  # 身份证号
        'source': 'pc'  # 无需修改
    }

    response = requests.get(url, headers=headers, params=params)
    json_data = json.loads(response.text)
    info = {
        '考试科目': item[int(km) - 1],
        '姓名': xm,
        '身份证号': no
    }
    if response.status_code == 200:
        if json_data['code'] == 404:
            info['分数'] = "查询失败"
        elif json_data['code'] == 403:
            info['分数'] = "查询功能已关闭"
        else:
            info['分数'] = int(json_data['score'])
    else:
        info['分数'] = f"GET请求失败, 状态码: {response.status_code}"

    print(item[int(km) - 1], xm, no, info['分数'])

    return info


def main():
    # 记录开始时间
    start_time = time.time()
    path = '数据/计通20学生花名册.xlsx'  # 替换为包含姓名和身份证号信息的Excel文件
    data = pd.read_excel(path)  # 原始信息
    # 生成一个新的Excel工作簿
    workbook = Workbook()
    CET4 = workbook.active
    CET4.title = 'CET4'
    CET6 = workbook.create_sheet(title='CET6')  # 创建并设置工作表的名称
    sheets = [CET4, CET6]
    # 设置表头
    titles = ['考试科目', '姓名', '身份证号', '分数']
    CET4.append(titles)
    CET6.append(titles)

    for item in [1, 2]:
        for row in data.iterrows():
            result = CETQuery(item, row[1]['姓名'], row[1]['身份证号'])  # 成绩查询
            if result['分数'] == "查询功能已关闭":
                return "查询功能已关闭"
            else:
                # 将查询结果添加到表格中
                sheets[item - 1].append([result['考试科目'], result['姓名'], result['身份证号'], result['分数']])

    # 保存Excel文件
    workbook.save(f'{path.replace(".xlsx","")}四六级成绩查询结果{datetime.now().strftime("%Y-%m-%d %H:%M:%S")}.xlsx')
    # 记录结束时间
    end_time = time.time()
    # 计算运行时间
    run_time = end_time - start_time
    print(f"文件已生成!运行时间: {run_time:.1f} 秒")


if __name__ == '__main__':
    main()
